package com.situ.company.util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.ResourceBundle;

public final class JDBCUtil {
    private JDBCUtil() {
    };

    private static ResourceBundle rb = ResourceBundle.getBundle("jdbc");
    private static String className = rb.getString("driver");
    private static String url = rb.getString("url");
    private static String user = rb.getString("user");
    private static String password = rb.getString("pass");

    static {
        try {
            Class.forName(className);// 加载驱动
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    /**
     * <p>Description: 获得数据库链接对象</p>  
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }
    
    
    /**
     * <p>Description: 关闭链接</p>  
     * @param conn
     * @param st
     * @param rs
     */
    public static void close(Connection conn, Statement st, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (st != null) {
                st.close();
                st = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    /**
     * <p>Description: 关闭链接</p>  
     * @param conn
     * @param st
     */
    public static void close(Connection conn, Statement st) {
        close(conn, st, null);
    }
    
    /**
     * <p>Title: update</p>  
     * <p>Description: DML</p>  
     * @param sql
     * @return
     */
    //数组中元素类型是引用类型=>asList返回的list的元素就是数组中的每个元素
    //数组中元素类型是基本数据类型=>asList返回的list的元素仅有一个，即这个数组
    
    public static Integer update(String sql, Object... vals) {
        return update(sql, Arrays.asList(vals));
    }
    
    public static Integer update(String sql, List<Object> vals) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtil.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < vals.size(); i++)
                ps.setObject(i+1, vals.get(i));
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close(conn, ps);
        }
        return null;
    }
    
    /**
     * <p>Description: DQL</p>  
     * @param <T>
     * @param sql
     *      执行的SQL语句
     * @param vals
     *      执行的SQL语句中的参数
     * @param clazz
     *      返回的集合中的元素类型的类描述
     * @param fields 
     *      key=属性名， value=字段名
     * @return
     */
    public static <T> List<T> queryList(String sql, List<Object> vals, Class<T> clazz, Map<String, String> fields) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<T> result = new ArrayList<>();
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < vals.size(); i++)
                ps.setObject(i + 1, vals.get(i));
            rs = ps.executeQuery();
            while(rs.next()) {
                T model = clazz.newInstance();
                for(Entry<String, String> entry : fields.entrySet()) {
                    Field field = clazz.getDeclaredField(entry.getKey());
                    field.setAccessible(true);
                    field.set(model, rs.getObject(entry.getValue()));
                }
                result.add(model);
            }
        } catch (SQLException | InstantiationException | IllegalAccessException | NoSuchFieldException | SecurityException e) {
            e.printStackTrace();
        }finally {
            close(conn, ps, rs);
        }
        return result;
    }
    
    /**
     * <p>Description: 数据库中的字段名和实例的属性名相同时</p>  
     * @param <T>
     * @param sql
     * @param vals
     * @param clazz
     * @param props
     * @return
     */
    public static <T> List<T> queryList(String sql, List<Object> vals, Class<T> clazz, List<String> props) {
        Map<String, String> fields = new HashMap<>();
        for ( String p : props) {
            fields.put(p, p);
        }
       return queryList(sql, vals, clazz, fields); 
    }
    
    //通过反射得到字段数组
    public static <T> List<T> queryList(String sql, List<Object> vals, Class<T> clazz) {
        Map<String, String> fields = new HashMap<>();
        for(Field p : clazz.getDeclaredFields())
            fields.put(p.getName(), p.getName());
        return queryList(sql, vals, clazz, fields); 
    }

    /**
     * <p>Title: jqueryModel</p>  
     * <p>Description: 获得单条记录</p>  
     * @param <T>
     * @param sql
     * @param vals
     * @param clazz
     * @param fields
     * @return
     */
    public static <T> T queryModel(String sql, List<Object> vals, Class<T> clazz, Map<String, String> fields) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < vals.size(); i++)
                ps.setObject(i + 1, vals.get(i));
            rs = ps.executeQuery();
            if(rs.next()) {
                T model = clazz.newInstance();
                for(Entry<String, String> entry : fields.entrySet()) {
                    Field field = clazz.getDeclaredField(entry.getKey());
                    field.setAccessible(true);
                    field.set(model, rs.getObject(entry.getValue()));
                }
                return model;
            }
        } catch (SQLException | InstantiationException | IllegalAccessException | NoSuchFieldException | SecurityException e) {
            e.printStackTrace();
        }finally {
            close(conn, ps, rs);
        }
        return null;
    }
    
    public static <T> T queryModel(String sql, List<Object> vals, Class<T> clazz, List<String> props) {
        Map<String, String> fields = new HashMap<>();
        for ( String p : props) {
            fields.put(p, p);
        }
       return queryModel(sql, vals, clazz, fields); 
    }
    
    public static <T> T queryModel(String sql, List<Object> vals, Class<T> clazz) {
        Map<String, String> fields = new HashMap<>();
        for(Field p : clazz.getDeclaredFields())
            fields.put(p.getName(), p.getName());
        return queryModel(sql, vals, clazz, fields); 
    }
    
    public static Integer queryInt(String sql, List<Object> vals) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
         try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < vals.size(); i++)
                ps.setObject(i+1, vals.get(i));
            rs = ps.executeQuery();
            if(rs.next())
                return rs.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } 
         return null;
    }
    
    /**
     * <p>Title: test</p>  
     * <p>Description: 测试数据库链接</p>  
     * @param args
     * @throws SQLException
     */
    public static void main(String[] args) throws SQLException {
        Connection conn = JDBCUtil.getConnection();
        System.out.println(conn);
        JDBCUtil.close(conn, null);
    }

}
